package com.wyu.test.repository;


import com.wyu.test.entity.CAddressEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

import java.util.Map;

public interface CAddressEntityRepository extends JpaRepository<CAddressEntity, Integer>, JpaSpecificationExecutor<CAddressEntity> {

    @Query(value = "SELECT c_user.username,c_user.phone,c_address.* FROM c_address LEFT JOIN c_user ON c_user.id = c_address.uid " +
            "WHERE c_user.id = ?1",
            nativeQuery = true)
    List<Map<String,Object>> NativeFindByUserId(int id);


    @Query(value = "SELECT c_user.username,c_user.phone,c_address.* FROM c_address LEFT JOIN c_user " +
            "ON c_user.id = c_address.uid WHERE IF(IFNULL(?1,'') !='',c_user.id = ?1,1 = 1) AND IF(?2 != '',c_user.username = ?2,1 = 1) AND " +
            "IF(?3 != '',c_user.phone = ?3,1 = 1) ",
    nativeQuery = true)
    Page<Map<String,Object>> NativeFindAllByUsernameOrPhone(Integer id,String username, String phone, Pageable pageable);
}
